CREATE  VIEW [dbo].[vReportIndexReport]
AS
SELECT
    D4.DocumentName AS ModuleName, D3.DocumentName AS RootFolderName,  
    D2.DocumentName AS FolderName, D1.DocumentName AS SubFolderName, D.DocumentName AS         ReportName, D.DocumentDescription AS Description, D.DocumentKey, H.HierarchyKey AS     DocumentNodeKey, H.ParentHierarchyKey AS ParentDocumentNodeKey
FROM dbo.DocumentMain D
    INNER JOIN dbo.Hierarchy H     ON D.DocumentVersionKey = H.UniformKey
    INNER JOIN dbo.Hierarchy H1    ON H.ParentHierarchyKey = H1.HierarchyKey
    INNER JOIN dbo.DocumentMain D1 ON H1.UniformKey = D1.DocumentVersionKey
    INNER JOIN dbo.Hierarchy H2    ON H1.ParentHierarchyKey = H2.HierarchyKey
    INNER JOIN dbo.DocumentMain D2 ON H2.UniformKey = D2.DocumentVersionKey
    INNER JOIN dbo.Hierarchy H3    ON H2.ParentHierarchyKey = H3.HierarchyKey
    INNER JOIN dbo.DocumentMain D3 ON H3.UniformKey = D3.DocumentVersionKey
    INNER JOIN dbo.Hierarchy H4    ON H3.ParentHierarchyKey = H4.HierarchyKey
    INNER JOIN dbo.DocumentMain D4 ON H4.UniformKey = D4.DocumentVersionKey
WHERE D.DocumentTypeCode = 'CRA'    AND D2.DocumentName = 'Reports'

GO